﻿ if object_id('fn_MapHTML_AssetXmissionStructure2') is not null
DROP FUNCTION fn_MapHTML_AssetXmissionStructure2

GO

CREATE FUNCTION fn_MapHTML_AssetXmissionStructure2
(
	@StructureId int
)
RETURNS varchar(max)
AS
BEGIN
	declare @img xml
	set @img = '<img src="http://dev-poleposition.com/DEV/Icons/transparent_150.png" />'

	declare @html varchar(max)
	set @html = 
	(


select
	 'Xmission Structure' as 'TITLE'
	,Structures.*

	-- structure images

	,(select
		 replace(Src.SettingValue, '{0}', Images.Utility_Asset_XMissionStructures_ImageId) as '@src'
		,replace(Href.SettingValue, '{0}', Images.Utility_Asset_XMissionStructures_ImageId) as '@href'
		,structures.Utility_Asset_XMissionStructureId as StructureId
		,images.Utility_Asset_XMissionStructures_ImageId as ImageId
	from
		tb_Utility_Asset_XMissionStructures structures
		join tb_Utility_Asset_XMissionStructures_Images images
			on images.Utility_Asset_XMissionStructureFK = structures.Utility_Asset_XMissionStructureId 
	for xml path ('IMAGE'), type) as '*'

	-- structure poles <POLE><TITLE /></POLE>

	,(select 
		 'Pole ' + convert(varchar, Poles.PoleRank) as 'TITLE'
		,Poles.* 

		-- pole images <IMAGE href="" src="" />

		,(select 
			 replace(Src.SettingValue, '{0}', Images.Utility_Asset_XMissionStructures_ImageId) as '@src'
			,replace(Href.SettingValue, '{0}', Images.Utility_Asset_XMissionStructures_ImageId) as '@href'
		from 
			tb_Utility_Asset_XMissionStructures_Poles as SubPoles
			join tb_Utility_Asset_XMissionStructures_Images as Images 
				on SubPoles.Utility_Asset_XMissionStructures_PoleId = Images.Utility_Asset_XMissionStructures_PoleFK
		where 
			SubPoles.Utility_Asset_XMissionStructures_PoleId = Poles.PoleId
		for xml path ('IMAGE'), type) as '*'

		-- pole gps visits <VISIT><TITLE /><VISIT>

		,(select 
			 'GPS Visit' as 'TITLE'
			,SubVisit.*

			-- visit images <IMAGE href="" src="" />

			,(select
				 replace(Src.SettingValue, '{0}', Images.Vendor_Visit_XMissionStructures_ImageId) as '@src'
				,replace(Href.SettingValue, '{0}', Images.Vendor_Visit_XMissionStructures_ImageId) as '@href'
				,Images.*
			from 
				tb_Vendor_Visit_XMissionStructures_Images as Images
			where 
				Images.Vendor_Visit_XMissionStructures_PoleFK = Poles.PoleId
			for xml path ('IMAGE'), type) as '*'

		from 
			tb_Vendor_Visit_XMissionStructures as SubVisit
			join tb_Vendor_Visit_XMissionStructures_Poles as SubPole
				on SubPole.Vendor_Visit_XMissionStructureFK = SubVisit.Vendor_Visit_XMissionStructureId
				and SubPole.Vendor_Visit_XMissionStructures_PoleId = Poles.PoleId
		for xml path ('VISIT'), type)

		-- pole inspection visits <VISIT><TITLE /><VISIT>

		,(select 
			 'Inspection Visit' as 'TITLE'
			,SubVisit.*
			,SubResults.*
			,SubInspectionVisit.*
			,SubResultType.*

			-- visit images <IMAGE href="" src="" />

			,(select
				 replace(Src.SettingValue, '{0}', Images.Vendor_InspVisit_XmissionStructures_ImageId) as '@src'
				,replace(Href.SettingValue, '{0}', Images.Vendor_InspVisit_XmissionStructures_ImageId) as '@href'
				,Images.*
			from 
				tb_Vendor_InspVisit_XmissionStructures_Images as Images
			where 
				Images.Vendor_InspVisit_XMissionStructure_PoleFk = Poles.PoleId
			for xml path ('IMAGE'), type) as '*'

		from 
			tb_Vendor_InspVisit_XMissionStructures as SubVisit
			join tb_Vendor_InspVisit_XmissionStructures_Poles as SubPole
				on SubPole.Vendor_InspVisit_XMissionStructureFk = SubVisit.Vendor_InspVisit_XMissionStructureId
				and SubPole.Utility_Asset_XMissionStructures_PoleFk = Poles.PoleId
			-- inspection results
			left join tb_Vendor_InspVisit_XmissionStructures_Results as SubResults
				on SubResults.Vendor_InspVisit_XMissionStructure_PoleFk = SubPole.Vendor_InspVisit_XMissionStructure_PoleId
			-- lookup/inspection type
			left join tb_Vendor_InspVisit_XmissionStructures_Inspection_Type SubInspectionVisit
				on SubInspectionVisit.Vendor_InspVisit_XmissionStructures_Inspection_TypeId = SubVisit.XMissionStructures_TopTypeFk
			-- lookup/result type
			left join tb_Vendor_InspVisit_XmissionStructures_ResultTypes as SubResultType
				on SubResultType.Vendor_InspVisit_XmissionStructures_ResultTypeId = SubResults.Vendor_InspVisit_XmissionStructures_ResultTypeFk
		for xml path ('VISIT'), type)

	from
		v_Utility_Asset_XmissionStructuresWithPoles as Poles
	where
		Poles.StructureId = Structures.Utility_Asset_XMissionStructureID
	for xml path ('POLE'), type) as '*'

from
	tb_Utility_Asset_XMissionStructures as Structures
	join tb_Settings as Href on Href.SettingId = 25
	join tb_Settings as Src on Src.SettingId = 24
where
	Structures.Utility_Asset_XMissionStructureId = @StructureId
for xml path ('STRUCTURE')
	
	)
	return @html

END
GO

select convert(xml, dbo.fn_MapHTML_AssetXmissionStructure2(40))
